package com.boc.ewr.business.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.boc.ewr.business.entity.EwrManageExam;
import org.apache.ibatis.annotations.Select;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @Description: 智慧e对-对账管理考核
 * @Author: jeecg-boot
 * @Date:   2023-06-08
 * @Version: V1.0
 */
public interface EwrManageExamMapper extends BaseMapper<EwrManageExam> {

    //条线履职-风险核查反馈
    @Select("select PARENT_BRANCH_NO, PARENT_BRANCH_NM,  BRANCH_NO, BRANCH_NM, COUNT(ID) as RISK_FEEBACK_NUM FROM ACCT_DTL_CLASS_C where CHECK_PSN IS NOT NULL AND IS_DELINQUENT = '1' AND CA_MON = #{caMon} GROUP BY PARENT_BRANCH_NO, PARENT_BRANCH_NM,  BRANCH_NO, BRANCH_NM")
    List<EwrManageExam> createRiskCInfo(@Param("caMon") String caMon);

    //条线履职-整改及时性
    //1、问题汇总”中的“未签约账户”、“已签约账户地址为空”
    @Select("select SIGNED_ORG as BRANCH_NO, COUNT(ID) as RECTIFICATION_NUM from PRO_PRBSD_LIST where SEND_STA in ('1', '2') GROUP BY SIGNED_ORG")
    List<EwrManageExam> createQuestOneInfo();
    @Select("select OBRANCH_NO as BRANCH_NO, COUNT(ID) as RECTIFICATION_NUM from SIGN_MISMATCH_FDBK GROUP BY OBRANCH_NO")
    List<EwrManageExam> createQuestTwoInfo();
    @Select("select BRANCH_NO as BRANCH_NO, COUNT(ID) as RECTIFICATION_NUM from EBANK_OPRATE_MISMATCH_FDBK GROUP BY BRANCH_NO")
    List<EwrManageExam> createQuestThrInfo();

    //条线履职-省行抽检
    @Select("select SC_PARENT_BRANCH_NO as PARENT_BRANCH_NO, SC_PARENT_BRANCH_NM as PARENT_BRANCH_NM，SC_BRANCH_NO as BRANCH_NO, SC_BRANCH_NM as BRANCH_NM, COUNT(ID) as PRO_INSPECTION_NUM FROM EWR_SPOT_CHECK where TO_DATE(#{caMon}, 'yyyyMM') BETWEEN TO_DATE(STARTTIME, 'yyyy-MM-dd') AND TO_DATE(ENDTIME, 'yyyy-MM-dd') GROUP BY SC_PARENT_BRANCH_NO, SC_PARENT_BRANCH_NM，SC_BRANCH_NO, SC_BRANCH_NM")
    List<EwrManageExam> createCheckInfo(@Param("caMon") String caMon);

    //网银渠道对账代客对账问题数
    //外网
    @Select("select ORG_NO as BRANCH_NO, COUNT(ID) as VALET_REC_NUM from EXTRANET_CA_FDBK where CA_MON = #{caMon} and IS_CA_BRK= '1' and FDBK_STA= '3' GROUP BY ORG_NO")
    List<EwrManageExam> createExterInfo(@Param("caMon") String caMon);
    //内网
    @Select("select ORG_NO as BRANCH_NO, COUNT(ID) as VALET_REC_NUM from INTRANET_CA_FDBK where CA_MON = #{caMon} and IS_CA_BRK= '1' and FDBK_STA= '3' GROUP BY ORG_NO")
    List<EwrManageExam> createInnerInfo(@Param("caMon") String caMon);

}
